/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package fullalbum.dao;

import fullalbum.TransactionDAO;
import fullalbum.Utils;
import fullalbum.bean.Figure;
import fullalbum.vo.UserRepeatedFigureSuggestedVO;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author leoomoreira
 */
public class FigureDAO {

    /**
     * Method that returns the three users of album
     * 
     * @param albumId
     * @return
     */
    public List<String[]> getThreeUsersFigureCountByAlbum(Integer albumId) {
        List<String[]> result = new ArrayList<String[]>();

        String sql = "select count(*) as total_figure, uaf.user_id from user_album_figure uaf where uaf.album_id = " + albumId + " group by uaf.user_id order by total_figure desc limit 3 offset 0";

        TransactionDAO dao = new TransactionDAO();
        ResultSet resultSet = dao.executeQuery(sql);

        try {
            while (resultSet != null && resultSet.next()) {
                String[] s = new String[] { resultSet.getString("user_id"), resultSet.getString("total_figure") };
                result.add(s);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return result;
    }

    /**
     * Method that returns a figure by albumId and figureId
     * 
     * @param albumId
     * @param figureId
     * @return
     */
    public Figure getByAlbumIdAndFigureId(Integer albumId, String figureId) {
        String sqlFigure = "select album_id, id from figure where id = '" + figureId + "' and album_id = " + albumId;

        Figure figure = new Figure();
        TransactionDAO dao = new TransactionDAO();

        ResultSet resultSetFigure = dao.executeQuery(sqlFigure);
        
        try {
            while (resultSetFigure != null && resultSetFigure.next()) {
                figure.setAlbumId(Integer.parseInt(resultSetFigure.getString("album_id")));
                figure.setId(resultSetFigure.getString("id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return figure;
    }

    /**
     * Method that insert a figure
     *
     * @param albumId
     * @param id
     * @return
     */
    public boolean insertFigure(Integer albumId, String id) {
        boolean result = false;
        String sql = "insert into figure (album_id, id) values (" + albumId + ", " + id + ")";

        TransactionDAO dao = new TransactionDAO();

        result = dao.insert(sql);

        if (result)
            dao.commit();
        else
            dao.rollback();
        dao.close();
        
        return result;
    }

    /**
     * Method that insert a figure on the user's album
     *
     * @param userId
     * @param albumId
     * @param figureId
     * @return
     */
    public boolean insertFigureOnUsersAlbum(Integer userId, Integer albumId, String figureId) {
        boolean result = false;
        String sql = "insert into user_album_figure (user_id, album_id, figure_id) values (" + userId + "," + albumId + ", '" + figureId + "')";

        TransactionDAO dao = new TransactionDAO();

        result = dao.insert(sql);

        if (result)
            dao.commit();
        else
            dao.rollback();
        dao.close();

        return result;
    }

    /**
     * Method that insert a repeated figure on the user's album
     *
     * @param userId
     * @param albumId
     * @param figureId
     * @return
     */
    public boolean insertRepeatedFigureOnUsersAlbum(Integer userId, Integer albumId, String figureId) {
        boolean result = false;
        String sql = "insert into repeated_user_album_figure (user_id, album_id, figure_id) values (" + userId + "," + albumId + ", '" + figureId + "')";

        TransactionDAO dao = new TransactionDAO();

        result = dao.insert(sql);

        if (result)
            dao.commit();
        else
            dao.rollback();
        dao.close();

        return result;
    }

    /**
     * Method that delete a figure on the user's album
     *
     * @param userId
     * @param albumId
     * @param figureId
     * @return
     */
    public int deleteFigureOnUsersAlbum(Integer userId, Integer albumId, String figureId) {
        int result = 0;
        String sql = "delete from user_album_figure where user_id = " + userId + " and album_id = " + albumId + " and figure_id = '" + figureId + "'";

        TransactionDAO dao = new TransactionDAO();

        result = dao.delete(sql);

        if (result > 0)
            dao.commit();
        else
            dao.rollback();
        dao.close();

        return result;
    }

    /**
     * Method that delete a repeated figure on the user's album
     *
     * @param userId
     * @param albumId
     * @param figureId
     * @return
     */
    public int deleteRepeatedFigureOnUsersAlbum(Integer userId, Integer albumId, String figureId) {
        int result = 0;
        String sql = "delete from repeated_user_album_figure where user_id = " + userId + " and album_id = " + albumId + " and figure_id = '" + figureId + "'";

        TransactionDAO dao = new TransactionDAO();

        result = dao.delete(sql);

        if (result > 0)
            dao.commit();
        else
            dao.rollback();
        dao.close();

        return result;
    }

    /**
     * Method that returns a list of figures existing in the user's album
     * 
     * @param userId
     * @param albumId
     * @return
     */
    public List<Figure> getFiguresWithinAlbum(Integer userId, Integer albumId) {
        String sqlFigures = "select album_id, user_id, figure_id from user_album_figure where user_id = " + userId + " and album_id = " + albumId + "";

        List<Figure> figureList = new ArrayList<Figure>();
        TransactionDAO dao = new TransactionDAO();

        ResultSet resultSetFigures = dao.executeQuery(sqlFigures);
        try {
            while (resultSetFigures != null && resultSetFigures.next()) {
                Figure figure = new Figure();

                String queryFigureId = resultSetFigures.getString("figure_id");
                String queryAlbumId = resultSetFigures.getString("album_id");

                figure.setId(queryFigureId);
                figure.setAlbumId(Integer.parseInt(queryAlbumId));

                figureList.add(figure);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return figureList;
    }

    /**
     * Method that returns a list of repeated figures existing in the user's album
     *
     * @param userId
     * @param albumId
     * @return
     */
    public List<Figure> getRepeatedFiguresWithinAlbum(Integer userId, Integer albumId) {
        String sqlFigures = "select album_id, user_id, figure_id from repeated_user_album_figure where user_id = " + userId + " and album_id = " + albumId + "";

        List<Figure> figureList = new ArrayList<Figure>();
        TransactionDAO dao = new TransactionDAO();

        ResultSet resultSetFigures = dao.executeQuery(sqlFigures);
        try {
            while (resultSetFigures != null && resultSetFigures.next()) {
                Figure figure = new Figure();

                String queryFigureId = resultSetFigures.getString("figure_id");
                String queryAlbumId = resultSetFigures.getString("album_id");

                figure.setId(queryFigureId);
                figure.setAlbumId(Integer.parseInt(queryAlbumId));

                figureList.add(figure);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return figureList;
    }

    /**
     * Method that returns a list of figure in the album
     *
     * @param albumId
     * @return
     */
    public List<Figure> getFiguresByAlbum(Integer albumId) {
        String sqlFigures = "select album_id, id from figure where album_id = " + albumId + "";

        List<Figure> figureList = new ArrayList<Figure>();
        TransactionDAO dao = new TransactionDAO();

        ResultSet resultSetFigures = dao.executeQuery(sqlFigures);
        try {
            while (resultSetFigures != null && resultSetFigures.next()) {
                Figure figure = new Figure();

                String queryId = resultSetFigures.getString("id");
                String queryAlbumId = resultSetFigures.getString("album_id");

                figure.setId(queryId);
                figure.setAlbumId(Integer.parseInt(queryAlbumId));

                figureList.add(figure);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return figureList;
    }

    /**
     * Method that returns a list of suggested figures for the albums the user
     *
     * @param userId
     * @return
     */
    public List<UserRepeatedFigureSuggestedVO> getRepeatedFiguresSuggested(Integer userId) {
        String sql = "select * from (" +
                "select ua.album_id, a.name as album_name, " +
                "ua.user_id, u.name as user_name, " +
                "(select count(*) from repeated_user_album_figure ruaf where ruaf.user_id = ua.user_id and ruaf.album_id = ua.album_id and ruaf.figure_id not in (select uaf.figure_id from user_album_figure uaf where uaf.user_id = " + userId + " and ruaf.album_id = ua.album_id)) as repeated_figure, " +
                "(select count(*) from repeated_user_album_figure ruaf where ruaf.user_id = " + userId + " and ruaf.album_id = ua.album_id and ruaf.figure_id not in (select uaf.figure_id from user_album_figure uaf where uaf.user_id = ua.user_id and ruaf.album_id = ua.album_id)) as exchange_figure " +
                "from user_album ua, album a, \"user\" u " +
                "where " +
                "ua.user_id <> " + userId + " " +
                "and exists (select * from user_album x where x.user_id = ua.user_id and x.album_id = ua.album_id) and ua.user_id = u.id and ua.album_id = a.id " +
                "order by ua.album_id asc, repeated_figure desc, exchange_figure desc) result where not (result.exchange_figure = 0 and result.repeated_figure = 0)";
        List<UserRepeatedFigureSuggestedVO> resultList = new ArrayList<UserRepeatedFigureSuggestedVO>();
        TransactionDAO dao = new TransactionDAO();

        ResultSet resultSetFigures = dao.executeQuery(sql);
        try {
            while (resultSetFigures != null && resultSetFigures.next()) {
                UserRepeatedFigureSuggestedVO userRepeatedFigureSuggestedVO = new UserRepeatedFigureSuggestedVO();

                String queryUserId = resultSetFigures.getString("user_id");
                String queryUserName = resultSetFigures.getString("user_name");
                String queryRepeatedFigure = resultSetFigures.getString("repeated_figure");
                String queryExchangeFigure = resultSetFigures.getString("exchange_figure");

                userRepeatedFigureSuggestedVO.setRepeatedFigureCount(Integer.parseInt(queryRepeatedFigure));
                userRepeatedFigureSuggestedVO.setExchangeFigureCount(Integer.parseInt(queryExchangeFigure));
                userRepeatedFigureSuggestedVO.setUserId(Integer.parseInt(queryUserId));
                userRepeatedFigureSuggestedVO.setUserName(queryUserName);

                resultList.add(userRepeatedFigureSuggestedVO);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return resultList;
    }

    /**
     * Method that returns a repeated figure list of the session user that can be exchanged with another user
     *
     * @param sessionUserId
     * @param userId
     * @param albumId
     * @return
     */
    public List<String> getRepeatedFiguresIdSuggested(Integer sessionUserId, Integer userId, Integer albumId) {
        String sql = "" +
                "select ruaf.figure_id " +
                "from repeated_user_album_figure ruaf " +
                "where " +
                "ruaf.user_id = " + userId + " " +
                "and ruaf.album_id = " + albumId + " " +
                "and ruaf.figure_id not in (select uaf.figure_id from user_album_figure uaf where uaf.user_id = " + sessionUserId + " and ruaf.album_id = " + albumId  + ")";
        List<Figure> resultFigure = new ArrayList<Figure>();
        TransactionDAO dao = new TransactionDAO();

        ResultSet resultSetFigures = dao.executeQuery(sql);
        try {
            while (resultSetFigures != null && resultSetFigures.next()) {
                Figure figure = new Figure();
                String queryFigureId = resultSetFigures.getString("figure_id");
                figure.setAlbumId(albumId);
                figure.setId(queryFigureId);
                resultFigure.add(figure);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return Utils.getSortFigureId(resultFigure);
    }

    /**
     * Method that returns a figure list of the session user that can be exchanged with another user
     *
     * @param sessionUserId
     * @param userId
     * @param albumId
     * @return
     */
    public List<String> getExchangeFiguresIdSuggested(Integer sessionUserId, Integer userId, Integer albumId) {
        String sql = "" +
                "select ruaf.figure_id " +
                "from repeated_user_album_figure ruaf " +
                "where " +
                "ruaf.user_id = " + sessionUserId + " " +
                "and ruaf.album_id = " + albumId + " " +
                "and ruaf.figure_id not in (select uaf.figure_id from user_album_figure uaf where uaf.user_id = " + userId + " and ruaf.album_id = " + albumId  + ")";
        List<Figure> resultFigure = new ArrayList<Figure>();
        TransactionDAO dao = new TransactionDAO();

        ResultSet resultSetFigures = dao.executeQuery(sql);
        try {
            while (resultSetFigures != null && resultSetFigures.next()) {
                Figure figure = new Figure();
                String queryFigureId = resultSetFigures.getString("figure_id");
                figure.setAlbumId(albumId);
                figure.setId(queryFigureId);
                resultFigure.add(figure);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return Utils.getSortFigureId(resultFigure);
    }

    /**
     * Method that returns a list of figures that are suggested to another user
     * 
     * @param sessionUserId
     * @param userId
     * @param albumId
     * @return
     */
    public List<Figure> getFiguresSuggestedToAnotherUser(Integer sessionUserId, Integer userId, Integer albumId) {
        String sql = "" +
                "select " +
                "ruaf.figure_id, " +
                "ruaf.album_id " +
                "from repeated_user_album_figure ruaf " +
                "where ruaf.user_id = " + sessionUserId + " " +
                "and ruaf.album_id = " + albumId + " " +
                "and ruaf.figure_id not in (" +
                    "select " +
                    "uaf.figure_id " +
                    "from user_album_figure uaf " +
                    "where uaf.user_id = " + userId + " " +
                    "and uaf.album_id = " + albumId + ")";
        
        List<Figure> resultList = new ArrayList<Figure>();

        TransactionDAO dao = new TransactionDAO();

        ResultSet resultSetFigures = dao.executeQuery(sql);
        
        try {
            while (resultSetFigures != null && resultSetFigures.next()) {
                Figure figure = new Figure();
                String queryFigureId = resultSetFigures.getString("figure_id");
                figure.setAlbumId(albumId);
                figure.setId(queryFigureId);
                resultList.add(figure);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FigureDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();

        return resultList;
    }
    
}
